<?php

/*
 * To change this template, choose Tools | Templates
* and open the template in the editor.
*/

class HT_Model_administrator_models_doctor extends Zend_Db_Table {//ten class fai viet hoa

	protected $_db;

	public function __construct() {
		$this->_name = "doctor";
		$this->_db = Zend_Registry::get('dbMain');
		parent::init();
	}
	
	public function addData($data){
		$this->insert($data);
		return $this->getMaxId();
	}
	
	public function getMaxId(){
		$sql = "SELECT MAX(id) FROM doctor";
		return  (int)$this->_db->fetchOne($sql);
	}
	public function getDoctor($doctorId,$filter = array()) {
		$sql = " SELECT * FROM doctor WHERE id= ".(int)$doctorId;
		return $this->_db->fetchRow($sql);
	}
	
	/*public function getAddedLocations($doctorId){
		$ids		= null;
		$sql 		= " SELECT location_id FROM link_doctor_location WHERE doctor_id= ".(int)$doctorId;
		$locations 	= $this->_db->fetchAll($sql);
		foreach((array)$locations as $location){
			$ids .= $location['location_id'].",";
		}
		if($ids) $ids = substr($ids, 0,-1);
		return $ids;
	}*/
	
	public function getListDoctor_nb($filter = array()) {
		$sqlPlus = $this->getListDoctor_sqlPlus($filter);
		$sql = "SELECT COUNT(id)
		FROM doctor
		WHERE 1=1 $sqlPlus";
		return $this->_db->fetchOne($sql);
	}
	public function getListDoctor($start=0,$size = 10,$filter = array()) {
		$sqlPlus = $this->getListDoctor_sqlPlus($filter);
		$sql = "SELECT *
		FROM doctor
		WHERE 1=1 $sqlPlus ORDER BY doctor_name LIMIT $start,$size";
		return $this->_db->fetchAll($sql);
	}
	
	private function getListDoctor_sqlPlus($filter){
		$sqlPlus = null;
		$keyword = trim(@$filter['keyword']);
		if($keyword){
			$sqlPlus .= " AND (doctor_name LIKE '%$keyword%' OR `doctor_phone` LIKE '%$keyword%' OR `doctor_email` LIKE '%$keyword%' OR `doctor_info` LIKE '%$keyword%') ";
		}
		return $sqlPlus;
	}
	
	public function getDoctorServices($doctorId){
		$services = array();
		$ids	  = null;
		$addedServices = $this->getAddedServices($doctorId);
		foreach($addedServices as $sv){
			$ids .= $sv['serviceId'].',';
		}
		if($ids) $ids = substr($ids,0,-1);
			
		$services['doctor'] 			= $this->getDoctor($doctorId);;
		$services['addedServices'] 	= $addedServices;
		$services['availableServices'] = $this->getAvailableServices($ids);;
		return $services;
	}
	
	public function getAvailableServices($ids){
		$sqlPlus = null;
		if($ids) $sqlPlus = " AND id not in ($ids) ";
		$sql = "SELECT id serviceId, ser_name serviceName
		FROM service
		WHERE type=2 $sqlPlus";
		return $this->_db->fetchAll($sql);
	}
	
	public function getAddedServices($doctorId){
		$sql = "SELECT sv.id serviceId, sv.ser_name serviceName
				FROM link_doctor_service lnk
				INNER JOIN  service sv ON sv.id = lnk.service_id
				WHERE sv.type=2 AND lnk.doctor_id= ".(int)$doctorId;
		return $this->_db->fetchAll($sql);
	}
	
	public function addDoctorService($doctorId,$serviceId){
		$this->_name = "link_doctor_service";
		$data = array('doctor_id'=>$doctorId,'service_id'=>$serviceId);
		$this->insert($data);
		$this->_name = "doctor";
		return true;
	}
	
	public function removeDoctorService($doctorId,$serviceId){
		$this->_name = "link_doctor_service";
		$this->delete('doctor_id='.(int)$doctorId.' AND service_id='.(int)$serviceId);
		$this->_name = "doctor";
		return true;
	}
	
	
	/* Doctor location */
	
	public function addDoctorLocation($doctorId,$locationId){
		$this->_name = "link_doctor_location";
		$data = array('doctor_id'=>$doctorId,'location_id'=>$locationId);
		$this->insert($data);
		$this->_name = "doctor";
		return true;
	}
	
	public function removeDoctorLocation($doctorId,$locationId){
		$this->_name = "link_doctor_location";
		$this->delete('doctor_id='.(int)$doctorId.' AND location_id='.(int)$locationId);
		$this->_name = "doctor";
		return true;
	}
	
	public function getDoctorLocations($locationId){
		$services = array();
		$ids	  = null;
		$addedServices = $this->getAddedLocations($locationId);
		foreach($addedServices as $sv){
			$ids .= $sv['serviceId'].',';
		}
		if($ids) $ids = substr($ids,0,-1);
			
		$services['location'] 			= $this->getLocation($locationId);;
		$services['addedServices'] 	= $addedServices;
		$services['availableServices'] = $this->getAvailableLocations($ids);;
		return $services;
	}
	
	public function getAvailableLocations($ids){
		$sqlPlus = null;
		if($ids) $sqlPlus = " AND location_id not in ($ids) ";
		$sql = "SELECT location_id locationId, location_name locationName
		FROM location
		WHERE location_type_id <> 1 $sqlPlus";
		return $this->_db->fetchAll($sql);
	}
	
	public function getAddedLocations($doctorId){
		$sql = "SELECT loc.location_id, loc.location_name
				FROM link_doctor_location lnk
				INNER JOIN  doctor dt ON lnk.doctor_id = dt.id
				INNER JOIN  location loc ON lnk.location_id = loc.location_id
				WHERE loc.location_type_id <> 1 AND doctor_id = ".(int)$doctorId;
		return $this->_db->fetchAll($sql);
	}
	
}

?>
